package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import play.db.DB;
import models.*;
import utils.exceptions.BDException;


public class AulaDAO {
	Connection conexao;
	
	public AulaDAO() {
		conexao = DB.getConnection();
	}
	
	public boolean cadastrar(Aula _aula) throws BDException {
		boolean retorno = false;	
		String comando = "insert into aula (codDisc, data, horaInicio, horaFim, idProfessor)"+
		"values(?,?,?,?,?)";
	
		try {
			PreparedStatement p = this.conexao.prepareStatement(comando);
			
			p.setString(1, _aula.getCodDisc());
			p.setDate(2, _aula.getData());
			p.setTime(3, _aula.getHoraInicio());
			p.setTime(4, _aula.getHoraFim());
			p.setLong(5, _aula.getIdProfessor());
			p.execute();			
			retorno = true;
		} catch (SQLException e) {
			throw new BDException("Ocorreu erro na hora de CADASTRAR a Aula (try catch)",e.getStackTrace());
		}
		
		return retorno;
	}

	public boolean editar(Aula _aula) throws BDException{
		String comando = "update aula set data=?, horaInicio=?, horaFim=? where idAula=?";
		
		try {
			PreparedStatement p = this.conexao.prepareStatement(comando);
			
			p.setDate(1, _aula.getData());
			p.setTime(2, _aula.getHoraInicio());
			p.setTime(3, _aula.getHoraFim());		
			p.setInt(4, _aula.getIdAula());
			
			p.execute();
			
			return true;
		} catch (SQLException e) {
			throw new BDException("Ocorreu erro na hora de EDITAR a Aula (try catch)",e.getStackTrace());
		}
			
	}

	public List<Aula> listarTodos() throws BDException {
		List<Aula> retorno = new ArrayList<Aula>();
		
		String comando = "select idAula, codDisc, data, horaInicio, horaFim from aula";
		
		try {
			java.sql.Statement start = conexao.createStatement();
			ResultSet rs = start.executeQuery(comando);
			
			while(rs.next()){
				Aula aula = new Aula();	
				aula.setIdAula(rs.getInt("idAula"));
				aula.setCodDisc(rs.getString("codDisc"));
				aula.setData(rs.getDate("data"));
				aula.setHoraInicio(rs.getTime("horaInicio"));				
				aula.setHoraFim(rs.getTime("horaFim"));				
			
				retorno.add(aula);
			}
		} catch (SQLException e) {
			throw new BDException("Ocorreu erro na hora de LISTAR a Aula (try catch)",e.getStackTrace());
		}
		
		return retorno;
	}
	
	public List<DisciplinaAula> listarTodosProf(long idProf){		
		List<DisciplinaAula> retorno = new ArrayList<DisciplinaAula>();
		String comando = "select D.idDisc, D.codDisc, D.nome, A.idAula, A.horaInicio, A.horaFim " +
						 "from disciplina D, aula A " +
						 "where A.idProfessor = '"+idProf+"' " +
						 "and D.codDisc = A.codDisc";
		try {
			ResultSet rs = DB.executeQuery(comando);
			while(rs.next()){				
				DisciplinaAula disc = new DisciplinaAula();
				disc.setIdDisc(rs.getLong("idDisc"));				
				disc.setCodDisc(rs.getString("codDisc"));
				disc.setNomeDisc(rs.getString("nome"));				
				disc.setIdAula(rs.getInt("idAula"));
				disc.setHoraInicio(rs.getTime("horaInicio"));
				disc.setHoraFim(rs.getTime("horaFim"));
				
				retorno.add(disc);
			}
		} catch (SQLException e) {
			//throw new BDException("Ocorreu erro na hora de LISTAR a Disciplina (try catch)",e.getStackTrace());
		}
		return retorno;		
	}

	public boolean excluir(int idAula) throws BDException {
		String comando1 = "delete from aula where idAula=?";		
		try {			
			PreparedStatement ps1 = this.conexao.prepareStatement(comando1);			
			ps1.setInt(1, idAula);			
			ps1.execute();			
			return true;
			
		} catch (SQLException e) {
			throw new BDException("Ocorreu erro na hora de EXCLUIR a Aula (try catch)",e.getStackTrace());
		}
	}

	public Aula buscarAula(int idAula) throws BDException {		
		Aula aula = new Aula();		
		String comando = "select * from aula where idAula = '"+idAula+"'";				
		try {
			java.sql.Statement start = conexao.createStatement();
			ResultSet rs = start.executeQuery(comando);			
			while(rs.next()){	
				aula.setIdAula(rs.getInt("idAula"));
				aula.setCodDisc(rs.getString("codDisc"));
				aula.setData(rs.getDate("data"));
				aula.setHoraInicio(rs.getTime("horaInicio"));
				aula.setHoraFim(rs.getTime("horaFim"));				
			}
		} catch (SQLException e) {
			throw new BDException("Ocorreu erro na hora de BUSCAR a Aula (try catch)",e.getStackTrace());
		}		
		return aula;
	}
	
	public List<Aula> buscarListaAula(String _codDisc) throws BDException {		
		List<Aula> retorno = new ArrayList<Aula>();							
		String comando = "select idAula, codDisc, data, horaInicio, horaFim from aula where codDisc = '"+_codDisc+"'";
		
		try {
			java.sql.Statement start = conexao.createStatement();
			ResultSet rs = start.executeQuery(comando);			
			while(rs.next()){
				Aula aula = new Aula();
				aula.setIdAula(rs.getInt("idAula"));
				aula.setCodDisc(rs.getString("codDisc"));
				aula.setData(rs.getDate("data"));
				aula.setHoraInicio(rs.getTime("horaInicio"));				
				aula.setHoraFim(rs.getTime("horaFim"));				
			
				retorno.add(aula);
			}
		} catch (SQLException e) {
			throw new BDException("Ocorreu erro na hora de LISTAR a Aula (try catch)",e.getStackTrace());
		}
		
		return retorno;
	}
	
}
